package uk.ac.cam.cl.oscar;

import java.sql.*;

import java.util.*;
import java.io.*;
import java.awt.image.BufferedImage;

import uk.ac.cam.cl.oscar.sift.*;
import uk.ac.cam.cl.oscar.edgefinder.*;

/**
 * DBAdaptor class used to represent interact with a database.
 */
public class DBAdaptor
{
  /** Address and port of the mysql server.  May be localhost:3306. */    
  private final static String addr = "jdbc:mysql://localhost/oscartest";
  // private final static String addr = "jdbc:mysql://localhost:3306/oscartest";
  private final static String driver = "org.gjt.mm.mysql.Driver";
  private final static String user = "root";
  private final static String pass = "q1w2e3r4";
  private final static String[] algorithms = {"SIFT"};

  /** Returns the JDBC result iterator for a given algorithm's hash set. */
  public static ResultSet getAlgorithmHashes(String algorithmId)
  {
    try {
        Statement stmt;

        // Setup the sql driver so JDBC can use it
        Class.forName(driver);

        // Connect to the database with the set address, user and pass
        Connection conn = DriverManager.getConnection(addr,user,pass);

        // Get all the algorithm hashes as blobs.  Do we want this converted?
        // Also, the algorithm Id isn't yet checked to see if the table exists
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + algorithmId);
       
        /** @BUG @TODO conn should be closed, but we need to return rs. make 
        	nice solution.
        	*/
        
        return rs;

    } catch(Exception e) {
      e.printStackTrace();
      return null;
    }
  }

  /** Retrieves the image alone from a database.
  	@WARNING will return null if id is not valid*/
  public static BufferedImage getImage(int id)
  {
  	BufferedImage image = null;
  
    try {
        Statement stmt;

        // Setup the sql driver so JDBC can use it
        Class.forName(driver);

        // Connect to the database with the set address, user and pass
        Connection conn = DriverManager.getConnection(addr,user,pass);

        // Get the image.  It's a blob, so we need to convert it
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT image FROM images WHERE id='" + id + "'");
        if (rs.next()) {
        	image = BufferedImagePacker.fromBlob (rs.getBlob("image"));
        }
        
        conn.close(); 
    } 
    catch(Exception e) {
      e.printStackTrace();
    }
    
    return image;
  }
  
  	/** @WARNING may return null if invalid id */
	public static ImageRecord getImageInfo (int id)
	{
		ImageRecord ir = null;
	
		try {
			Statement stmt;
		
			// Setup the sql driver so JDBC can use it
			Class.forName(driver);
		
			// Connect to the database with the set address, user and pass
			Connection conn = DriverManager.getConnection(addr,user,pass);
		
			// Get the image.  It's a blob, so we need to convert it
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT image, artist, posterid, posterblurb, cost FROM images WHERE id='" + id + "'");
			
			if (rs.next()) {		
				// Extract binary data
				BufferedImage image = BufferedImagePacker.fromBlob (rs.getBlob("image"));
				ir = new ImageRecord(image, rs.getString ("artist"), rs.getString("posterid"),
					rs.getString("posterblurb"), rs.getString("cost"));
			}
			else {
				System.out.println ("Cannot get info on image " + id + " as it is not in the database");
			}
			conn.close();
				
		} catch(Exception e) {
			e.printStackTrace();
		}
	
	return ir;
	}

	public static void insertImage(ImageRecord ir, int id)
	{
		BufferedImage img = ir.getThumbnail();
		
		String statement = "INSERT INTO images (image, artist, posterblurb, posterid, cost, id) VALUES (";
		statement += "?, ";
		statement += "'" + ir.getArtist() + "', ";
		statement += "'" + ir.getPosterBlurb() + "', ";
		statement += "'" + ir.getPosterId() + "', ";
		statement += "'" + ir.getCost() + "', ";
		statement += "" + id + " );";
		
		Connection conn = null;
		
		try {
			// Setup the sql driver so JDBC can use it
			Class.forName(driver);
			
			// Connect to the database with the set address, user and pass
			conn = DriverManager.getConnection(addr,user,pass);
			
			// Get a list of all the tables and just delete them all
			PreparedStatement stmt = conn.prepareStatement(statement);
			
			// Add binary data
			stmt.setObject (1, BufferedImagePacker.toByteArray(img));
			
			stmt.executeUpdate();
			conn.close();
		} 
		catch(Exception e) {
			e.printStackTrace();
		}
	}

	public static void insertHash(String algorithm, byte[] hash, int id)
	{
		String statement = "INSERT INTO "+algorithm+" (id, hash) VALUES (?, ?)";

		Connection conn = null;
		try {
			// Setup the sql driver so JDBC can use it
			Class.forName(driver).newInstance();
			
			// Connect to the database with the set address, user and pass
			conn = DriverManager.getConnection(addr,user,pass);
			
			// Get a list of all the tables and just delete them all
			PreparedStatement stmt = conn.prepareStatement(statement);
			
			// Fill out binary data
			stmt.setInt (1, id);
			stmt.setObject (2, hash);
			
			stmt.executeUpdate();
			conn.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	public static void createTables()
	{
		try {
        Statement stmt;

        // Setup the sql driver so JDBC can use it
        Class.forName(driver);

        // Connect to the database with the set address, user and pass
        Connection conn = DriverManager.getConnection(addr,user,pass);

        // Get a list of all the tables and just delete them all
        stmt = conn.createStatement();
      	stmt.executeUpdate("CREATE TABLE IF NOT EXISTS images (image BLOB, artist VARCHAR(30), posterblurb TINYTEXT, posterid VARCHAR(30), cost VARCHAR(10), id INT UNIQUE KEY);");
      	
      	for (String i : algorithms)
      		stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " + i + " (hash LONGBLOB, id INT UNIQUE KEY);");
      		
        conn.close();
    } catch(Exception e) {
      e.printStackTrace();
    }
  }

	/* Adds .jpg files to database */
	public static void populate (String imageDir)
	{
		try {
			dropTables();
			createTables();
		
			File directory = new File (imageDir);
			File[] files = directory.listFiles();
			
			int count = 0;
			
			for (File f : files) 
			{
				if (f.getName().endsWith(".jpg")) 
				{					
					/* Lets add and hash! */
					BufferedImage image = ImageOperations.load (f);
					//BufferedImage rect = EdgeFinder.extractRectangle (image);
					BufferedImage rect = image;
					BufferedImage thumb = ImageOperations.createThumbnail (rect);
					ImageRecord ir = new ImageRecord (thumb, "Luke Woods", f.getName(), "A classic rendition of " + f.getName(), "3.50 GBP");
						
					insertImage (ir, count);
					
					Vector<Keypoint> kps = SIFT.findKeypoints (rect);
					byte[] kpb = KeypointPacker.toByteArray(kps);
					
					insertHash ("SIFT", kpb, count);
					
					System.out.println ("Added " + f.getName() + " (" + kps.size() + " keypoints) to the database");
					
					count++;
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}


	public static void clearTables ()
	{
		try {
			Statement stmt;
			
			// Setup the sql driver so JDBC can use it
			Class.forName(driver);
			
			// Connect to the database with the set address, user and pass
			Connection conn = DriverManager.getConnection(addr,user,pass);
	
			// Get a list of all the tables and just delete them all
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SHOW TABLES");
			while(rs.next()) {
				conn.createStatement().executeUpdate("TRUNCATE TABLE " + rs.getString(1) );
			}
			conn.close();
		} 
		catch(Exception e) {
			e.printStackTrace();
		}	
	}
	

  public static void dropTables()
  {
    try {
        Statement stmt;

        // Setup the sql driver so JDBC can use it
        Class.forName(driver);

        // Connect to the database with the set address, user and pass
        Connection conn = DriverManager.getConnection(addr,user,pass);

        // Get a list of all the tables and just delete them all
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW TABLES");
        while(rs.next()) {
			conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + rs.getString(1) );
        }
		conn.close();
	} 
	catch (SQLException e) {
		System.out.println (e.getSQLState());
		e.printStackTrace();
	}
	catch (Exception e) {
		e.printStackTrace();
    }
  }
  
}
